﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MSSQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.SQLserver
{
    public class Securities : ISecurities
    {
        private SqlConnection _internalConnection;
        private SqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        //SQL string

        #region Parameters
        private const string PARM_STOCKNO = "@STOCKNO";
        private const string PARM_STOCKSYMBOL = "@STOCKSYMBOL";
        private const string PARM_STOCKTYPE = "@STOCKTYPE";
        private const string PARM_CEILING = "@CEILING";
        private const string PARM_FLOOR = "@FLOOR";
        private const string PARM_BIGLOTVALUE = "@BIGLOTVALUE";
        private const string PARM_SECURITIESNAME = "@SECURITIESNAME";
        private const string PARM_SECTORNO = "@SECTORNO";
        private const string PARM_DESIGNATED = "@DESIGNATED";
        private const string PARM_SUSPENSION = "@SUSPENSION";
        private const string PARM_DELIST = "@DELIST";
        private const string PARM_HALTRESUMEFLAG = "@HALTRESUMEFLAG";
        private const string PARM_SPLIT = "@SPLIT";
        private const string PARM_BENEFIT = "@BENEFIT";
        private const string PARM_MEETING = "@MEETING";
        private const string PARM_NOTICE = "@NOTICE";
        private const string PARM_CLIENTIDREQUEST = "@CLIENTIDREQUEST";
        private const string PARM_COUPONRATE = "@COUPONRATE";
        private const string PARM_ISSUEDATE = "@ISSUEDATE";
        private const string PARM_MATUREDATE = "@MATUREDATE";
        private const string PARM_AVRPRICE = "@AVRPRICE";
        private const string PARM_PARVALUE = "@PARVALUE";
        private const string PARM_SDCFLAG = "@SDCFLAG";
        private const string PARM_PRIORCLOSEPRICE = "@PRIORCLOSEPRICE";
        private const string PARM_PRIORCLOSEDATE = "@PRIORCLOSEDATE";
        private const string PARM_PROJECTOPEN = "@PROJECTOPEN";
        private const string PARM_OPENPRICE = "@OPENPRICE";
        private const string PARM_LAST = "@LAST";
        private const string PARM_LASTVOL = "@LASTVOL";
        private const string PARM_LASTVAL = "@LASTVAL";
        private const string PARM_HIGHEST = "@HIGHEST";
        private const string PARM_LOWEST = "@LOWEST";
        private const string PARM_TOTALSHARE = "@TOTALSHARE";
        private const string PARM_TOTALVALUE = "@TOTALVALUE";
        private const string PARM_ACCUMULATEDEAL = "@ACCUMULATEDEAL";
        private const string PARM_BIGDEAL = "@BIGDEAL";
        private const string PARM_BIGVOL = "@BIGVOL";
        private const string PARM_BIGVAL = "@BIGVAL";
        private const string PARM_TOTALVOL = "@TOTALVOL";
        private const string PARM_BEST3BID = "@BEST3BID";
        private const string PARM_BEST3BIDVOLUME = "@BEST3BIDVOLUME";
        private const string PARM_BEST2BID = "@BEST2BID";
        private const string PARM_BEST2BIDVOLUME = "@BEST2BIDVOLUME";
        private const string PARM_BEST1BID = "@BEST1BID";
        private const string PARM_BEST1BIDVOLUME = "@BEST1BIDVOLUME";
        private const string PARM_BEST1OFFER = "@BEST1OFFER";
        private const string PARM_BEST1OFFERVOLUME = "@BEST1OFFERVOLUME";
        private const string PARM_BEST2OFFER = "@BEST2OFFER";
        private const string PARM_BEST2OFFERVOLUME = "@BEST2OFFERVOLUME";
        private const string PARM_BEST3OFFER = "@BEST3OFFER";
        private const string PARM_BEST3OFFERVOLUME = "@BEST3OFFERVOLUME";
        private const string PARM_BOARDLOST = "@BOARDLOST";

        private const string PARM_TIME = "@TIME";
        private const string PARM_VNINDEX = "@VNINDEX";
        private const string PARM_TOTALTRADE = "@TOTALTRADE";
        private const string PARM_TOTALSHARES = "@TOTALSHARES";
        private const string PARM_TOTALVALUES = "@TOTALVALUES";
        private const string PARM_UPVOLUME = "@UPVOLUME";
        private const string PARM_DOWNVOLUME = "@DOWNVOLUME";
        private const string PARM_NOCHANGEVOLUME = "@NOCHANGEVOLUME";
        private const string PARM_ADVANCES = "@ADVANCES";
        private const string PARM_DECLINES = "@DECLINES";
        private const string PARM_NOCHANGE = "@NOCHANGE";
        private const string PARM_MARKET = "@MARKET";
        private const string PARM_ODDVOL = "@ODDVOL";
        private const string PARM_ODDVAL = "@ODDVAL";

        private const string PARM_TOTALROOM = "@TOTALROOM";
        private const string PARM_CURRENTROOM = "@CURRENTROOM";
        private const string PARM_CONTROLCODE = "@CONTROLCODE";
        private const string PARM_ACCUMULATEDVOL = "@ACCUMULATEDVOL";
        private const string PARM_ACCUMULATEDVAL = "@ACCUMULATEDVAL";
        private const string PARM_PRICE = "@PRICE";
        private const string PARM_CONFIRMNO = "@CONFIRMNO";
        private const string PARM_STOCKNAME = "@STOCKNAME";
        private const string PARM_BOARD = "@BOARD";
        private const string PARM_TRADEID = "@TRADEID";
        private const string PARM_FIRMNO = "@FIRMNO";
        private const string PARM_FLAG = "@FLAG";
        private const string PARM_VOL = "@VOL";
        private const string PARM_SIDE = "@SIDE";
        private const string PARM_VN50INDEX = "@VN50INDEX";
        private const string PARM_MARKETID = "@MARKETID";
        private const string PARM_FILTER = "@FILTER";
        private const string PARM_TRANSDATE = "@TRANSDATE";

        #endregion

        #region ISecurities Members

        public void InsertDeList(int stockNo, string symbol, string stockType, string stockName, int sectorNo)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_STOCKSYMBOL, SqlDbType.VarChar, 8),
                                                new SqlParameter(PARM_STOCKTYPE, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_STOCKNAME, SqlDbType.VarChar,25),
                                                new SqlParameter(PARM_SECTORNO, SqlDbType.Int,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = stockName;
                parms[4].Value = sectorNo;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertDeList", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountDeList()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountDeList"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteDeList()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteDeList");
            }
            catch
            {
                throw;
            }
        }

        public void InsertFroom(long stockNo, double totalRoom, double currentRoom)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_TOTALROOM, SqlDbType.Float, 8),
                                                new SqlParameter(PARM_CURRENTROOM, SqlDbType.Float,8)};
                parms[0].Value = stockNo;
                parms[1].Value = totalRoom;
                parms[2].Value = currentRoom;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertFroom", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountFroom()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountFroom"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteFroom()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteFroom");
            }
            catch
            {
                throw;
            }
        }

        public DataTable getFroom()
        {
            return null;
        }
        public void InsertLeCompact(int stockno, int price, int volume, int volp1, int valp1, int volp2, int valp2, int time)
        {
        }
        public void InsertLe(long stockNo, long price, long accumulateVol, double accumulateVal, long highest, long lowest, long time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_PRICE, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_ACCUMULATEDVOL, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_ACCUMULATEDVAL, SqlDbType.Float, 8),
                                                new SqlParameter(PARM_HIGHEST, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_LOWEST, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_TIME, SqlDbType.BigInt,4)};
                parms[0].Value = stockNo;
                parms[1].Value = price;
                parms[2].Value = accumulateVol;
                parms[3].Value = accumulateVal;
                parms[4].Value = highest;
                parms[5].Value = lowest;
                parms[6].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertLe", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountLe()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountLe"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteLe()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteLe");
            }
            catch
            {
                throw;
            }
        }

        public void InsertMarketStat(string controlCode, long time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_CONTROLCODE, SqlDbType.VarChar, 4),
                                                new SqlParameter(PARM_TIME, SqlDbType.BigInt, 8)};
                parms[0].Value = controlCode;
                parms[1].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertMarketStat", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountMarketStat()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountMarketStat"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteMarketStat()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteMarketStat");
            }
            catch
            {
                throw;
            }
        }

        public void InsertNewList(int stockNo, string symbol, string stockType, string stockName, int sectorNo)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_STOCKSYMBOL, SqlDbType.VarChar, 8),
                                                new SqlParameter(PARM_STOCKTYPE, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_STOCKNAME, SqlDbType.VarChar,25),
                                                new SqlParameter(PARM_SECTORNO, SqlDbType.Int,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = stockName;
                parms[4].Value = sectorNo;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertNewList", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountNewList()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountNewList"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteNewList()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteNewList");
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutAd(int tradeid, int stockNo, long vol, double price, long firmno, string side, string board, long time, string flag)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_TRADEID, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_VOL, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_PRICE, SqlDbType.Float,8),
                                                new SqlParameter(PARM_FIRMNO, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_SIDE, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_BOARD, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_TIME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_FLAG, SqlDbType.VarChar,1)};
                parms[0].Value = tradeid;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = firmno;
                parms[5].Value = side;
                parms[6].Value = board;
                parms[7].Value = time;
                parms[8].Value = flag;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertPutAd", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutAd()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountPutAd"));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutAd()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeletePutAd");
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutDc(int confirmNo, int stockNo, long vol, long price, string board)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_CONFIRMNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.VarChar, 2),
                                                new SqlParameter(PARM_VOL, SqlDbType.VarChar,4),
                                                new SqlParameter(PARM_PRICE, SqlDbType.VarChar,8),
                                                new SqlParameter(PARM_BOARD, SqlDbType.Int,1)};
                parms[0].Value = confirmNo;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = board;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertPutDc", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutDc()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountPutDc"));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutDc()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeletePutDc");
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutExec(long confirmNo, int stockNo, long vol, long price, string board)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_CONFIRMNO, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_VOL, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_PRICE, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BOARD, SqlDbType.VarChar,1)};
                parms[0].Value = confirmNo;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = board;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertPutDc", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutExec()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountPutExec"));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutExec()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeletePutExec");
            }
            catch
            {
                throw;
            }
        }

        public void InsertSecurities(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_STOCKNO, SqlDbType.Int, 2),
                                                new SqlParameter(PARM_STOCKSYMBOL, SqlDbType.VarChar, 8),
                                                new SqlParameter(PARM_STOCKTYPE, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_CEILING, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_FLOOR, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BIGLOTVALUE, SqlDbType.Float,8),
                                                new SqlParameter(PARM_SECURITIESNAME, SqlDbType.VarChar,25),
                                                new SqlParameter(PARM_SECTORNO, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_DESIGNATED, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_SUSPENSION, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_DELIST, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_HALTRESUMEFLAG, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_SPLIT, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_BENEFIT, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_MEETING, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_NOTICE, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_CLIENTIDREQUEST, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_COUPONRATE, SqlDbType.Int,2),
                                                new SqlParameter(PARM_ISSUEDATE, SqlDbType.VarChar,6),
                                                new SqlParameter(PARM_MATUREDATE, SqlDbType.VarChar,6),
                                                new SqlParameter(PARM_AVRPRICE, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_PARVALUE, SqlDbType.Int,2),
                                                new SqlParameter(PARM_SDCFLAG, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_PRIORCLOSEPRICE, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_PRIORCLOSEDATE, SqlDbType.VarChar,6),
                                                new SqlParameter(PARM_PROJECTOPEN, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_OPENPRICE, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_LAST, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_LASTVOL, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_LASTVAL, SqlDbType.Float,8),
                                                new SqlParameter(PARM_HIGHEST, SqlDbType.Int,4),
                                                new SqlParameter(PARM_LOWEST, SqlDbType.Int,4),
                                                new SqlParameter(PARM_TOTALSHARE, SqlDbType.Float,8),
                                                new SqlParameter(PARM_TOTALVALUE, SqlDbType.Float,8),
                                                new SqlParameter(PARM_ACCUMULATEDEAL, SqlDbType.Int,2),
                                                new SqlParameter(PARM_BIGDEAL, SqlDbType.Int,2),
                                                new SqlParameter(PARM_BIGVOL, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BIGVAL, SqlDbType.Float,8),
                                                new SqlParameter(PARM_ODDVOL, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_ODDVAL, SqlDbType.Float,8),
                                                new SqlParameter(PARM_BEST1BID, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST1BIDVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BEST2BID, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST2BIDVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BEST3BID, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST3BIDVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BEST1OFFER, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST1OFFERVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BEST2OFFER, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST2OFFERVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BEST3OFFER, SqlDbType.BigInt, 4),
                                                new SqlParameter(PARM_BEST3OFFERVOLUME, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_BOARDLOST, SqlDbType.Int,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = ceiling;
                parms[4].Value = floor;
                parms[5].Value = bigLotValue;
                parms[6].Value = securitiesName;
                parms[7].Value = sectorNo;
                parms[8].Value = designated;
                parms[9].Value = suspension;
                parms[10].Value = delist;
                parms[11].Value = haltResumeFlag;
                parms[12].Value = split;
                parms[13].Value = benefit;
                parms[14].Value = meeting;
                parms[15].Value = notice;
                parms[16].Value = clientIdRequest;
                parms[17].Value = couponRate;
                parms[18].Value = issueDate;
                parms[19].Value = matureDate;
                parms[20].Value = avrPrice;
                parms[21].Value = parValue;
                parms[22].Value = SDCFlag;
                parms[23].Value = PriorClosePrice;
                parms[24].Value = priorCloseDate;
                parms[25].Value = projectOpen;
                parms[26].Value = openPrice;
                parms[27].Value = last;
                parms[28].Value = lastVol;
                parms[29].Value = lastVal;
                parms[30].Value = highest;
                parms[31].Value = lowest;
                parms[32].Value = totalShare;
                parms[33].Value = totalValue;
                parms[34].Value = AccumulateDeal;
                parms[35].Value = bigDeal;
                parms[36].Value = bigVol;
                parms[37].Value = bigVal;
                parms[38].Value = oddVol;
                parms[39].Value = oddVal;
                parms[40].Value = best1Bid;
                parms[41].Value = best1BidVolume;
                parms[42].Value = best2Bid;
                parms[43].Value = best2BidVolume;
                parms[44].Value = best3Bid;
                parms[45].Value = best3BidVolume;
                parms[46].Value = best1Offer;
                parms[47].Value = best1OfferVolume;
                parms[48].Value = best2Offer;
                parms[49].Value = best2OfferVolume;
                parms[50].Value = best3Offer;
                parms[51].Value = best3OfferVolume;
                parms[52].Value = boardLost;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertSecurities", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountSecurities()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountSecurities"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteSecurities()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteSecurities");
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSecuritiesByStockSymbol(string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long projectOpen, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost, long openPrice, string symbol)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter(PARM_DESIGNATED, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_SUSPENSION, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_DELIST, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_HALTRESUMEFLAG, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_SPLIT, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_BENEFIT, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_MEETING, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_NOTICE, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_CLIENTIDREQUEST, SqlDbType.VarChar,1),
                                        new SqlParameter(PARM_COUPONRATE, SqlDbType.Int,2),
                                        new SqlParameter(PARM_ISSUEDATE, SqlDbType.VarChar,6),
                                        new SqlParameter(PARM_MATUREDATE, SqlDbType.VarChar,6),
                                        new SqlParameter(PARM_AVRPRICE, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_PARVALUE, SqlDbType.Int,2),
                                        new SqlParameter(PARM_SDCFLAG, SqlDbType.VarChar,1),

                                        new SqlParameter(PARM_PROJECTOPEN, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_LAST, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_LASTVOL, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_LASTVAL, SqlDbType.Float,8),
                                        new SqlParameter(PARM_HIGHEST, SqlDbType.Int,4),
                                        new SqlParameter(PARM_LOWEST, SqlDbType.Int,4),
                                        new SqlParameter(PARM_TOTALSHARE, SqlDbType.Float,8),
                                        new SqlParameter(PARM_TOTALVALUE, SqlDbType.Float,8),
                                        new SqlParameter(PARM_ACCUMULATEDEAL, SqlDbType.Int,2),
                                        new SqlParameter(PARM_BIGDEAL, SqlDbType.Int,2),
                                        new SqlParameter(PARM_BIGVOL, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BIGVAL, SqlDbType.Float,8),
                                        new SqlParameter(PARM_ODDVOL, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_ODDVAL, SqlDbType.Float,8),
                                        new SqlParameter(PARM_BEST1BID, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST1BIDVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BEST2BID, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST2BIDVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BEST3BID, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST3BIDVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BEST1OFFER, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST1OFFERVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BEST2OFFER, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST2OFFERVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BEST3OFFER, SqlDbType.BigInt, 4),
                                        new SqlParameter(PARM_BEST3OFFERVOLUME, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_BOARDLOST, SqlDbType.Int,2),
                                        new SqlParameter(PARM_OPENPRICE, SqlDbType.BigInt,4),
                                        new SqlParameter(PARM_STOCKSYMBOL, SqlDbType.VarChar, 8)
};
                parms[0].Value = designated;
                parms[1].Value = suspension;
                parms[2].Value = delist;
                parms[3].Value = haltResumeFlag;
                parms[4].Value = split;
                parms[5].Value = benefit;
                parms[6].Value = meeting;
                parms[7].Value = notice;
                parms[8].Value = clientIdRequest;
                parms[9].Value = couponRate;
                parms[10].Value = issueDate;
                parms[11].Value = matureDate;
                parms[12].Value = avrPrice;
                parms[13].Value = parValue;
                parms[14].Value = SDCFlag;
                parms[15].Value = projectOpen;
                parms[16].Value = last;
                parms[17].Value = lastVol;
                parms[18].Value = lastVal;
                parms[19].Value = highest;
                parms[20].Value = lowest;
                parms[21].Value = totalShare;
                parms[22].Value = totalValue;
                parms[23].Value = AccumulateDeal;
                parms[24].Value = bigDeal;
                parms[25].Value = bigVol;
                parms[26].Value = bigVal;
                parms[27].Value = oddVol;
                parms[28].Value = oddVal;
                parms[29].Value = best1Bid;
                parms[30].Value = best1BidVolume;
                parms[31].Value = best2Bid;
                parms[32].Value = best2BidVolume;
                parms[33].Value = best3Bid;
                parms[34].Value = best3BidVolume;
                parms[35].Value = best1Offer;
                parms[36].Value = best1OfferVolume;
                parms[37].Value = best2Offer;
                parms[38].Value = best2OfferVolume;
                parms[39].Value = best3Offer;
                parms[40].Value = best3OfferVolume;
                parms[41].Value = boardLost;
                parms[42].Value = openPrice;
                parms[43].Value = symbol;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "UpdateSecuritiesByStockSymbol", parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertTotalMkt(long vnIndex, long totalTrade, double totalShares, double totalVlues, double upVolume, double noChangeVolume, double downVolume, int advances, int declines, int noChange, long vn50index, string marketid, string filter, long time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                                new SqlParameter(PARM_VNINDEX, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_TOTALTRADE, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_TOTALSHARES, SqlDbType.Float,8),
                                                new SqlParameter(PARM_TOTALVALUES, SqlDbType.Float,8),
                                                new SqlParameter(PARM_UPVOLUME, SqlDbType.Float,8),
                                                new SqlParameter(PARM_NOCHANGEVOLUME, SqlDbType.Float,8),
                                                new SqlParameter(PARM_DOWNVOLUME, SqlDbType.Float,8),
                                                new SqlParameter(PARM_ADVANCES, SqlDbType.Int,2),
                                                new SqlParameter(PARM_NOCHANGE, SqlDbType.Int,2),
                                                new SqlParameter(PARM_DECLINES, SqlDbType.Int,2),
                                                new SqlParameter(PARM_VN50INDEX, SqlDbType.BigInt,4),
                                                new SqlParameter(PARM_MARKETID, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_FILTER, SqlDbType.VarChar,1),
                                                new SqlParameter(PARM_TIME, SqlDbType.BigInt,4)};
                parms[0].Value = vnIndex;
                parms[1].Value = totalTrade;
                parms[2].Value = totalShares;
                parms[3].Value = totalVlues;
                parms[4].Value = upVolume;
                parms[5].Value = noChangeVolume;
                parms[6].Value = downVolume;
                parms[7].Value = advances;
                parms[8].Value = noChange;
                parms[9].Value = declines;
                parms[10].Value = vn50index;
                parms[11].Value = marketid;
                parms[12].Value = filter;
                parms[13].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertTotalMkt", parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountTotalMkt()
        {
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "CountTotalMkt"));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteTotalMkt()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteTotalMkt");
            }
            catch
            {
                throw;
            }
        }

        #endregion
        public void UpdateFroom(long stockNo, double totalRoom, double currentRoom, int buy, int sell)
        {
            
        }
        public string getSecuritiesNameBySymbol(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_STOCKSYMBOL, SqlDbType.VarChar, 8);
                parm1.Value = symbol;

                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectSecuritiesNameBySymbol", parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return rdr["SecuritiesName"].ToString();
                }
                else
                    return String.Empty;
            }
            catch
            {
                throw;
            }
        }
        public List<string> getSecuritiesSymbol()
        {
            try
            {
                List<string> list = new List<string>();

                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectSecuritiesSymbol");
                while (rdr.Read())
                {
                    list.Add(rdr["StockSymbol"].ToString().Trim());
                }
                return list;
            }
            catch
            {
                throw;
            }
        }
        public int CountBackupSecurities(DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "Count_Backup_Securities", parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToInt32(rdr["RECNUM"]);
                }
                else
                    return 0;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Works on transaction date only
        /// </summary>
        public void Backup()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_BackupHose");
            }
            catch
            {
                throw;
            }
        }
        public void Clear()
        {
            try
            {
                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_ClearHose");
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Update the session table. Create and update each session information 
        /// </summary>
        /// <param name="stockNo"></param>
        /// <param name="transDate"></param>
        public void UpdateATOByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_STOCKNO, SqlDbType.Int);
                parm1.Value = stockNo;
                SqlParameter parm2 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm2.Value = transDate;
                SqlParameter[] parms = { parm1, parm2 };

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "UpdateHoseSessionATOByStockNo", parms);
            }
            catch
            {
                throw;
            }
        }
        public void UpdateATCByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_STOCKNO, SqlDbType.Int);
                parm1.Value = stockNo;
                SqlParameter parm2 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm2.Value = transDate;
                SqlParameter[] parms = { parm1, parm2 };

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "UpdateHoseSessionATCByStockNo", parms);
            }
            catch
            {
                throw;
            }
        }
        public void UpdateSession2ByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_STOCKNO, SqlDbType.Int);
                parm1.Value = stockNo;
                SqlParameter parm2 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm2.Value = transDate;
                SqlParameter[] parms = { parm1, parm2 };

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "UpdateHoseSession2ByStockNo", parms);
            }
            catch
            {
                throw;
            }
        }
        public void InitSessionData(DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "UpdateHoseSessionInitialize", parm1);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSecuritiesEarly(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {

        }

        public void UpdateSecuritiesLately(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {

        }

        #region ISecurities Members


        public void InsertFroomOrigin(long stockNo, double totalRoom, double currentRoom)
        {
            throw new NotImplementedException();
        }

        public int CountFroomOrigin()
        {
            throw new NotImplementedException();
        }

        public void DeleteFroomOrigin()
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}

